package com.carbeauty.repository;

import com.carbeauty.entity.Customer;
import com.carbeauty.common.enums.BusinessStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 客户数据访问接口
 * 
 * 提供客户相关的数据库操作方法:
 * - 基础CRUD操作
 * - 按条件查询客户
 * - 客户统计分析
 * - 会员管理查询
 * 
 * @author CarBeauty Team
 */
@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {
    
    // ==================== 基础查询方法 ====================
    
    /**
     * 根据手机号查找客户
     */
    Optional<Customer> findByPhone(String phone);
    
    /**
     * 根据会员卡号查找客户
     */
    Optional<Customer> findByCardNo(String cardNo);
    
    /**
     * 根据车牌号查找客户
     */
    List<Customer> findByCarNumber(String carNumber);
    
    /**
     * 检查手机号是否存在
     */
    boolean existsByPhone(String phone);
    
    /**
     * 检查会员卡号是否存在
     */
    boolean existsByCardNo(String cardNo);
    
    // ==================== 按状态查询 ====================
    
    /**
     * 查找所有活跃客户
     */
    List<Customer> findByStatus(BusinessStatus status);
    
    /**
     * 按创建时间倒序查找活跃客户
     */
    List<Customer> findByStatusOrderByCreateTimeDesc(BusinessStatus status);
    
    // ==================== 模糊查询 ====================
    
    /**
     * 根据客户姓名模糊查询
     */
    List<Customer> findByNameContainingAndStatus(String name, BusinessStatus status);
    
    /**
     * 根据手机号模糊查询
     */
    List<Customer> findByPhoneContainingAndStatus(String phone, BusinessStatus status);
    
    /**
     * 多条件模糊查询
     */
    @Query("SELECT c FROM Customer c WHERE " +
           "(:name IS NULL OR c.name LIKE %:name%) AND " +
           "(:phone IS NULL OR c.phone LIKE %:phone%) AND " +
           "(:carNumber IS NULL OR c.carNumber LIKE %:carNumber%) AND " +
           "(:memberLevel IS NULL OR c.memberLevel = :memberLevel) AND " +
           "c.status = :status")
    List<Customer> findByConditions(@Param("name") String name,
                                   @Param("phone") String phone,
                                   @Param("carNumber") String carNumber,
                                   @Param("memberLevel") String memberLevel,
                                   @Param("status") BusinessStatus status);
    
    // ==================== 会员等级查询 ====================
    
    /**
     * 根据会员等级查找客户
     */
    List<Customer> findByMemberLevelAndStatus(String memberLevel, BusinessStatus status);
    
    /**
     * 统计各会员等级客户数量
     */
    @Query("SELECT c.memberLevel, COUNT(c) FROM Customer c WHERE c.status = 'ACTIVE' GROUP BY c.memberLevel")
    List<Object[]> countByMemberLevel();
    
    /**
     * 查找VIP客户（黄金会员和钻石会员）
     */
    @Query("SELECT c FROM Customer c WHERE c.memberLevel IN ('黄金会员', '钻石会员') AND c.status = 'ACTIVE'")
    List<Customer> findVipCustomers();
    
    // ==================== 余额和积分查询 ====================
    
    /**
     * 查找余额大于指定金额的客户
     */
    List<Customer> findByBalanceGreaterThanAndStatus(BigDecimal minBalance, BusinessStatus status);
    
    /**
     * 查找积分大于指定数量的客户
     */
    List<Customer> findByPointsGreaterThanAndStatus(Integer minPoints, BusinessStatus status);
    
    /**
     * 按余额倒序查找客户
     */
    List<Customer> findByStatusOrderByBalanceDesc(BusinessStatus status);
    
    /**
     * 按积分倒序查找客户
     */
    List<Customer> findByStatusOrderByPointsDesc(BusinessStatus status);
    
    /**
     * 查找余额不足的客户
     */
    @Query("SELECT c FROM Customer c WHERE c.balance < :threshold AND c.status = 'ACTIVE'")
    List<Customer> findLowBalanceCustomers(@Param("threshold") BigDecimal threshold);
    
    // ==================== 消费统计查询 ====================
    
    /**
     * 按累计消费金额倒序查找客户
     */
    List<Customer> findByStatusOrderByTotalConsumptionDesc(BusinessStatus status);
    
    /**
     * 按消费次数倒序查找客户
     */
    List<Customer> findByStatusOrderByConsumptionCountDesc(BusinessStatus status);
    
    /**
     * 查找消费金额大于指定金额的客户
     */
    List<Customer> findByTotalConsumptionGreaterThanAndStatus(BigDecimal minConsumption, BusinessStatus status);
    
    /**
     * 查找消费次数大于指定次数的客户
     */
    List<Customer> findByConsumptionCountGreaterThanAndStatus(Integer minCount, BusinessStatus status);
    
    /**
     * 查找高价值客户（消费金额前N名）
     */
    @Query("SELECT c FROM Customer c WHERE c.status = 'ACTIVE' ORDER BY c.totalConsumption DESC")
    List<Customer> findTopCustomersByConsumption(org.springframework.data.domain.Pageable pageable);
    
    // ==================== 活跃度分析 ====================
    
    /**
     * 查找最近消费的客户
     */
    List<Customer> findByLastConsumptionTimeAfterAndStatus(LocalDateTime cutoffTime, BusinessStatus status);
    
    /**
     * 查找长时间未消费的客户
     */
    @Query("SELECT c FROM Customer c WHERE " +
           "(c.lastConsumptionTime IS NULL OR c.lastConsumptionTime < :cutoffTime) " +
           "AND c.status = 'ACTIVE'")
    List<Customer> findInactiveCustomers(@Param("cutoffTime") LocalDateTime cutoffTime);
    
    /**
     * 按最后消费时间倒序查找客户
     */
    List<Customer> findByStatusOrderByLastConsumptionTimeDesc(BusinessStatus status);
    
    /**
     * 查找活跃客户（最近30天有消费）
     */
    @Query("SELECT c FROM Customer c WHERE c.lastConsumptionTime >= :thirtyDaysAgo AND c.status = 'ACTIVE'")
    List<Customer> findActiveCustomers(@Param("thirtyDaysAgo") LocalDateTime thirtyDaysAgo);
    
    // ==================== 车辆信息查询 ====================
    
    /**
     * 根据车辆品牌查找客户
     */
    List<Customer> findByCarBrandAndStatus(String carBrand, BusinessStatus status);
    
    /**
     * 根据车辆型号查找客户
     */
    List<Customer> findByCarModelAndStatus(String carModel, BusinessStatus status);
    
    /**
     * 根据车辆颜色查找客户
     */
    List<Customer> findByCarColorAndStatus(String carColor, BusinessStatus status);
    
    /**
     * 统计各车辆品牌客户数量
     */
    @Query("SELECT c.carBrand, COUNT(c) FROM Customer c WHERE c.carBrand IS NOT NULL AND c.status = 'ACTIVE' GROUP BY c.carBrand")
    List<Object[]> countByCarBrand();
    
    // ==================== 客户来源分析 ====================
    
    /**
     * 根据客户来源查找客户
     */
    List<Customer> findBySourceAndStatus(String source, BusinessStatus status);
    
    /**
     * 统计各客户来源数量
     */
    @Query("SELECT c.source, COUNT(c) FROM Customer c WHERE c.source IS NOT NULL AND c.status = 'ACTIVE' GROUP BY c.source")
    List<Object[]> countBySource();
    
    /**
     * 查找推荐客户
     */
    List<Customer> findByReferrerIsNotNullAndStatus(BusinessStatus status);
    
    // ==================== 统计查询 ====================
    
    /**
     * 统计活跃客户总数
     */
    long countByStatus(BusinessStatus status);
    
    /**
     * 统计今日新增客户数量
     */
    @Query("SELECT COUNT(c) FROM Customer c WHERE DATE(c.createTime) = CURRENT_DATE AND c.status = 'ACTIVE'")
    long countTodayNewCustomers();
    
    /**
     * 统计本月新增客户数量
     */
    @Query("SELECT COUNT(c) FROM Customer c WHERE YEAR(c.createTime) = YEAR(CURRENT_DATE) AND MONTH(c.createTime) = MONTH(CURRENT_DATE) AND c.status = 'ACTIVE'")
    long countThisMonthNewCustomers();
    
    /**
     * 统计客户总余额
     */
    @Query("SELECT SUM(c.balance) FROM Customer c WHERE c.status = 'ACTIVE'")
    BigDecimal sumTotalBalance();
    
    /**
     * 统计客户总积分
     */
    @Query("SELECT SUM(c.points) FROM Customer c WHERE c.status = 'ACTIVE'")
    Long sumTotalPoints();
    
    /**
     * 统计客户总消费金额
     */
    @Query("SELECT SUM(c.totalConsumption) FROM Customer c WHERE c.status = 'ACTIVE'")
    BigDecimal sumTotalConsumption();
    
    /**
     * 计算平均消费金额
     */
    @Query("SELECT AVG(c.totalConsumption) FROM Customer c WHERE c.status = 'ACTIVE' AND c.totalConsumption > 0")
    BigDecimal avgConsumption();
    
    /**
     * 计算平均消费次数
     */
    @Query("SELECT AVG(c.consumptionCount) FROM Customer c WHERE c.status = 'ACTIVE' AND c.consumptionCount > 0")
    Double avgConsumptionCount();
}
